home *** CD-ROM | disk | FTP | other *** search
/ Winzipper / Winzipper_ISO.iso / programming / oracle7 7.2 / DB / UTIL72 / UTLDTREE.SQL < prev    next >
Encoding:
Text File  |  1995-05-09  |  4.8 KB  |  139 lines
  1. Rem 
  2. Rem $Header: utldtree.sql 7020100.1 94/09/23 22:14:31 cli Generic<base> $ 
  3. Rem 
  4. Rem  Copyright (c) 1991 by Oracle Corporation 
  5. Rem    NAME
  6. Rem      deptree.sql - Show objects recursively dependent on given object
  7. Rem    DESCRIPTION
  8. Rem      This procedure, view and temp table will allow you to see all
  9. Rem      objects that are (recursively) dependent on the given object.
  10. Rem      Note: you will only see objects for which you have permission.
  11. Rem      Examples:
  12. Rem        execute deptree_fill('procedure', 'scott', 'billing');
  13. Rem        select * from deptree order by seq#;
  14. Rem
  15. Rem        execute deptree_fill('table', 'scott', 'emp');
  16. Rem        select * from deptree order by seq#;
  17. Rem
  18. Rem        execute deptree_fill('package body', 'scott', 'accts_payable');
  19. Rem        select * from deptree order by seq#;
  20. Rem
  21. Rem        A prettier way to display this information than
  22. Rem        select * from deptree order by seq#;
  23. Rem       is
  24. Rem             select * from ideptree;
  25. Rem        This shows the dependency relationship via indenting.  Notice
  26. Rem        that no order by clause is needed with ideptree.
  27. Rem    RETURNS
  28. Rem 
  29. Rem    NOTES
  30. Rem      Run this script once for each schema that needs this utility.
  31. Rem      
  32. Rem    MODIFIED   (MM/DD/YY)
  33. Rem     rkooi      10/26/92 -  owner -> schema for SQL2 
  34. Rem     glumpkin   10/20/92 -  Renamed from DEPTREE.SQL 
  35. Rem     rkooi      09/02/92 -  change ORU errors 
  36. Rem     rkooi      06/10/92 -  add rae errors 
  37. Rem     rkooi      01/13/92 -  update for sys vs. regular user 
  38. Rem     rkooi      01/10/92 -  fix ideptree 
  39. Rem     rkooi      01/10/92 -  Better formatting, add ideptree view 
  40. Rem     rkooi      12/02/91 -  deal with cursors 
  41. Rem     rkooi      10/19/91 -  Creation 
  42.  
  43. drop sequence deptree_seq
  44. /
  45. create sequence deptree_seq cache 200 /* cache 200 to make sequence faster */
  46. /
  47. drop table deptree_temptab
  48. /
  49. create table deptree_temptab
  50. (
  51.   object_id            number,
  52.   referenced_object_id number,
  53.   nest_level           number,
  54.   seq#                 number      
  55. )
  56. /
  57. create or replace procedure deptree_fill (type char, schema char, name char) is
  58.   obj_id number;
  59. begin
  60.   delete from deptree_temptab;
  61.   commit;
  62.   select object_id into obj_id from all_objects
  63.     where owner        = upper(deptree_fill.schema)
  64.     and   object_name  = upper(deptree_fill.name)
  65.     and   object_type  = upper(deptree_fill.type);
  66.   insert into deptree_temptab
  67.     values(obj_id, 0, 0, 0);
  68.   insert into deptree_temptab
  69.     select object_id, referenced_object_id,
  70.         level, deptree_seq.nextval
  71.       from public_dependency
  72.       connect by prior object_id = referenced_object_id
  73.       start with referenced_object_id = deptree_fill.obj_id;
  74. exception
  75.   when no_data_found then
  76.     raise_application_error(-20000, 'ORU-10013: ' ||
  77.       type || ' ' || schema || '.' || name || ' was not found.');
  78. end;
  79. /
  80.  
  81. drop view deptree
  82. /
  83.  
  84. set echo on
  85.  
  86. REM This view will succeed if current user is sys.  This view shows 
  87. REM which shared cursors depend on the given object.  If the current
  88. REM user is not sys, then this view get an error either about lack
  89. REM of privileges or about the non-existence of table x$kglxs.
  90.  
  91. set echo off
  92. create view sys.deptree
  93.   (nested_level, type, schema, name, seq#)
  94. as
  95.   select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#
  96.   from deptree_temptab d, dba_objects o
  97.   where d.object_id = o.object_id (+)
  98. union all
  99.   select d.nest_level+1, 'CURSOR', '<shared>', '"'||c.kglnaobj||'"', d.seq#+.5
  100.   from deptree_temptab d, x$kgldp k, x$kglob g, obj$ o, user$ u, x$kglob c,
  101.       x$kglxs a
  102.     where d.object_id = o.obj#
  103.     and   o.name = g.kglnaobj
  104.     and   o.owner# = u.user#
  105.     and   u.name = g.kglnaown
  106.     and   g.kglhdadr = k.kglrfhdl
  107.     and   k.kglhdadr = a.kglhdadr   /* make sure it is not a transitive */
  108.     and   k.kgldepno = a.kglxsdep   /* reference, but a direct one */
  109.     and   k.kglhdadr = c.kglhdadr
  110.     and   c.kglhdnsp = 0 /* a cursor */
  111. /
  112.  
  113. set echo on
  114.  
  115. REM This view will succeed if current user is not sys.  This view
  116. REM does *not* show which shared cursors depend on the given object.
  117. REM If the current user is sys then this view will get an error 
  118. REM indicating that the view already exists (since prior view create
  119. REM will have succeeded).
  120.  
  121. set echo off
  122. create view deptree
  123.   (nested_level, type, schema, name, seq#)
  124. as
  125.   select d.nest_level, o.object_type, o.owner, o.object_name, d.seq#
  126.   from deptree_temptab d, all_objects o
  127.   where d.object_id = o.object_id (+)
  128. /
  129.  
  130. drop view ideptree
  131. /
  132. create view ideptree (dependencies)
  133. as
  134.   select lpad(' ',3*(max(nested_level))) || max(nvl(type, '<no permission>')
  135.     || ' ' || schema || decode(type, NULL, '', '.') || name)
  136.   from deptree
  137.   group by seq# /* So user can omit sort-by when selecting from ideptree */
  138. /
  139.